package com.Spring.SpringSpms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import com.Spring.SpringSpms.model.Designation;

public class DesignationDAOImpl implements DesignationDAO {

	private JdbcTemplate jdbcTemplate;
	
	public DesignationDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void saveOrUpdate(Designation designation) {
		if (designation.getDesignation_id() > 0) {
			// update
			String sql = "UPDATE designation SET designation_name=? "
						+ "WHERE designation_id=?";
			jdbcTemplate.update(sql, designation.getDesignation_name(), designation.getDesignation_id());
		} else {
			// insert
			String sql = "INSERT INTO designation (designation_name)"
						+ " VALUES (?)";
			jdbcTemplate.update(sql, designation.getDesignation_name());
		}

	}

	@Override
	public void delete(int designationId) {
		String sql = "DELETE FROM designation WHERE designation_id=?";
		jdbcTemplate.update(sql, designationId);
	}

	@Override
	public Designation get(int designationId) {
		
		String sql = "SELECT * FROM designation WHERE designation_id=" + designationId;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Designation>() {

			@Override
			public Designation extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Designation designation = new Designation();
					designation.setDesignation_id(rs.getInt("designation_id"));
					designation.setDesignation_name(rs.getString("designation_name"));	
					return designation;
				}
				
				return null;
			}
			
		});

	}

	@Override
	public List<Designation> list() {
		
		String sql = "SELECT * FROM designation";
		List<Designation> listDesignation = jdbcTemplate.query(sql, new RowMapper<Designation>() {

			@Override
			public Designation mapRow(ResultSet rs, int rowNum) throws SQLException {
				Designation designation = new Designation();
				designation.setDesignation_id(rs.getInt("designation_id"));
				designation.setDesignation_name(rs.getString("designation_name"));	
				return designation;
			}
			
		});
		
		return listDesignation;

	}

}